/**
 * author:Administrator
 * date:2013-5-21
 */
package com.roc.web.action.estrn;

import java.math.BigDecimal;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.Utils;
import com.roc.util.constant.Role;

/**
 * @author Administrator
 *
 */
public class EstrnHisInfoSearchCondition extends BaseSearchCondition {
	   
//		private String reversal;  //状态
		private String minDate;		
		private String maxDate;		
		private String merNo;  //商户号		
		private String merName;  //商户名		
		private String etsterMid;  //终端号		
		private String etstranCode;  //交易类型		
		private String respCode;   //交易返回码		
		private String minRMB;   //交易范围		
		private String maxRMB;		
		private String tranDate;   //交易日期		
	    private String opId;		
//		private String obj;		
		private String orgCode;
		private String account;//交易账号		
		private String agentId;		
		private String roleType;//角色类型		
		private String marketorId;
		private String ucbccdArea;		
		private String retriRefNo;//系统跟踪号
		private String mchntCdConv;  //后端商户号
		private String retriRefNoConv;//交易参考号

		/* (non-Javadoc)
		 * @see com.roc.page.BaseSearchCondition#getOrderPartOfHQL()
		 */
		@Override
		protected String getOrderPartOfHQL() {
			// TODO Auto-generated method stub
			
			//return " ORDER BY t.TRANS_DT DESC,t.TRANS_MT DESC ";
			return " ";
		}

		/* (non-Javadoc)
		 * @see com.roc.page.BaseSearchCondition#getRowCountHQL()
		 */
		@Override
		protected String getRowCountHQL() {
			StringBuffer sb = new StringBuffer();
			
			if (roleType.equals(Role.AGENT.getCode() )||roleType.equals(Role.SFB_AGENT.getCode() )) {
				sb = sb.append(" select count(1) ")
						.append(" from apstfr.tbl_tfr_his_trn_log t,tbl_mer_agent_relation tm ")
						.append(" where t.MCHNT_CD  = tm.merno  ")
						.append(" and tm.agentid=").append(agentId);
			}else if(roleType.equals(Role.MARKET_DIRECT.getCode()) || roleType.equals(Role.MARKET_LEAD.getCode())){//销售人员
				sb = sb.append(" select count(1) ")
						.append(" from apstfr.tbl_tfr_his_trn_log t,TBL_MCHT_INF tm ")
						.append(" where t.MCHNT_CD = tm.mcht_cd  ")
						.append(" and exists (select 1 from t_oper_role tor where tm.uc_bc_cd_area = tor.agentid and tor.operid ="+marketorId+") ");
			}else{
				sb = sb.append(" select count(1) ")
						.append(" from apstfr.tbl_tfr_his_trn_log t,TBL_MCHT_INF  tm")
						.append(" where t.MCHNT_CD  = tm.mcht_cd  ");
						 
			}
			return sb.toString();
		}

		/* (non-Javadoc)
		 * @see com.roc.page.BaseSearchCondition#getSearchHQL()
		 */
		@Override
		protected String getSearchHQL() {
			StringBuffer sb = new StringBuffer();
			
			if (roleType.equals(Role.AGENT.getCode() )||roleType.equals(Role.SFB_AGENT.getCode() )) {
				sb = sb.append("select t.TRANS_DT,t.TRANS_MT,t.TRAN_TP, ")
						.append("t.RESP_CD,t.TRANS_ST,t.PRI_ACCT_NO,t.CARD_CLASS,'', ")
//						.append("(case when t.card_class = '01' then '借记卡' else '信用卡' end) cardType, ")
						.append("t.TRANS_AT,tm.MERNAME,t.MCHNT_CD,t.TERM_ID,t.MA_TRANS_SEQ,t.RETRI_REF_NO, ")
						.append("t.pos_entry_md_cd,t.MA_TRANS_CD,t.KEY_RSP ,t.mchnt_cd_conv, t.retri_ref_no_conv,t.msg_resv_fld1 ")
						.append(" from apstfr.tbl_tfr_his_trn_log t,tbl_mer_agent_relation tm")
						//.append(" where Translate(t.MCHNT_CD USING CHAR_CS) = tm.merno  ")
						.append(" where t.MCHNT_CD  = tm.merno  ")
						.append(" and tm.agentid=").append(agentId);
			}else if(roleType.equals(Role.MARKET_DIRECT.getCode()) || roleType.equals(Role.MARKET_LEAD.getCode())){//销售人员
				sb = sb.append("select t.TRANS_DT,t.TRANS_MT,t.TRAN_TP, ")
						.append("t.RESP_CD,t.TRANS_ST,t.PRI_ACCT_NO,t.CARD_CLASS,'', ")
//						.append("(case when t.card_class = '01' then '借记卡' else '信用卡' end) cardType, ")
						.append("t.TRANS_AT,tm.NAME_BUSI,t.MCHNT_CD,t.TERM_ID,t.MA_TRANS_SEQ,t.RETRI_REF_NO, ")
						.append("t.pos_entry_md_cd,t.MA_TRANS_CD,t.KEY_RSP ,t.mchnt_cd_conv, t.retri_ref_no_conv,t.msg_resv_fld1 ")
						.append(" from apstfr.tbl_tfr_his_trn_log t,TBL_MCHT_INF tm ")
						.append(" where t.MCHNT_CD = tm.mcht_cd  ")
						.append(" and exists (select 1 from t_oper_role tor where tm.uc_bc_cd_area = tor.agentid and tor.operid ="+marketorId+") ");
			}else{
				sb = sb.append("select t.TRANS_DT,t.TRANS_MT,t.TRAN_TP, ")
						.append("t.RESP_CD,t.TRANS_ST,t.PRI_ACCT_NO,t.CARD_CLASS,'', ")
//						.append("(case when t.card_class = '01' then '借记卡' else '信用卡' end) cardType, ")
						.append("t.TRANS_AT,tm.NAME_BUSI,t.MCHNT_CD,t.TERM_ID,t.MA_TRANS_SEQ,t.RETRI_REF_NO, ")
						.append("t.pos_entry_md_cd,t.MA_TRANS_CD,t.KEY_RSP ,t.mchnt_cd_conv, t.retri_ref_no_conv,t.msg_resv_fld1 ")
						.append(" from apstfr.tbl_tfr_his_trn_log t,TBL_MCHT_INF  tm")
						.append(" where t.MCHNT_CD  = tm.mcht_cd  ");
						 
			}
			
			
			return sb.toString();
		}

		/* (non-Javadoc)
		 * @see com.roc.page.BaseSearchCondition#getWhereHQLFieldBeanList()
		 */
		@Override
		protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
			List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();

			
			
			/*if (Constance.isNotEmpty(reversal)) {
			    if(!reversal.equals("99")){
			    	filedBeanList.add(new WhereHQLFieldBean("t.TRANS_ST", reversal.trim(), "=", false));	
			    }
			}*/
			
			//终端号
			if (Constance.isNotEmpty(etsterMid)) {
				filedBeanList.add(new WhereHQLFieldBean("t.TERM_ID", etsterMid.trim(), "=", false));
			}
			
			//商户号
			if (Constance.isNotEmpty(merNo)) {
				filedBeanList.add(new WhereHQLFieldBean("t.MCHNT_CD", merNo.trim(), "=", false));
			}
			//后端商户号
			if (Constance.isNotEmpty(mchntCdConv)) {
				filedBeanList.add(new WhereHQLFieldBean("t.mchnt_cd_conv", mchntCdConv.trim(), "=", false));
			}
			
			//所属机构
			if (Constance.isNotEmpty(orgCode)) {
					filedBeanList.add(new WhereHQLFieldBean("tm.BANK_BELONG_CD", orgCode.trim(), "=", false));
			}
			
			//账号
			if (Constance.isNotEmpty(account)) {
				filedBeanList.add(new WhereHQLFieldBean("t.PRI_ACCT_NO", Utils.replace(account.trim(), "*", "_"), "LIKE", false,"ALL"));
			}
			if (Constance.isNotEmpty(merName)) {
				if (roleType.equals(Role.AGENT.getCode() )) {
					filedBeanList.add(new WhereHQLFieldBean("tm.MERNAME", merName.trim(),"LIKE", false, "ALL"));
				}else{
					filedBeanList.add(new WhereHQLFieldBean("tm.NAME_BUSI", merName.trim(),"LIKE", false, "ALL"));
				}
				
			}
			
			if (Constance.isNotEmpty(etstranCode)) {
			    if(!etstranCode.equals("99")){
			    	filedBeanList.add(new WhereHQLFieldBean("trim(t.MA_TRANS_CD)", etstranCode, "=", false));	
			    }
			}
			
			if (Constance.isNotEmpty(respCode) && !"0".equals(respCode)) {
				if (!respCode.equals("9999")) {
					filedBeanList.add(new WhereHQLFieldBean("t.RESP_CD", respCode,"=", false));
				} else {
					// 交易失败 respCD=9999
					filedBeanList.add(new WhereHQLFieldBean("t.RESP_CD", "00","<>", false));
				}
				// filedBeanList.add(new WhereHQLFieldBean("t.RESP_CD", respCode,"=", false));
			}
			
			if(Constance.isNotEmpty(minRMB)){
				BigDecimal bigMin = new BigDecimal(minRMB);
				bigMin = bigMin.multiply(new BigDecimal(100));
	         	filedBeanList.add(new WhereHQLFieldBean("to_number(t.TRANS_AT)",bigMin.toString(),">=", false));
	        }
			if(Constance.isNotEmpty(maxRMB)){
				BigDecimal bigMax = new BigDecimal(maxRMB);
				bigMax = bigMax.multiply(new BigDecimal(100));
	       	    filedBeanList.add(new WhereHQLFieldBean("to_number(t.TRANS_AT)",bigMax.toString(),"<=", false));
	        }
			
			
			/*if(Constance.isNotEmpty(minDate)){
	         	//filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts ","to_date('"+minDate.trim()+"', 'yyyymmdd')",">=", false));
	         	//filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts","to_date('"+minDate.trim()+"', 'yyyymmdd')+ 1","<", false));
	         	
	         	filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts >= to_date('"+minDate.trim()+"', 'yyyymmdd')"));
	         	filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts < to_date('"+minDate.trim()+"', 'yyyymmdd' )+ 1 "));
	       	}*/
			
			filedBeanList.add(new WhereHQLFieldBean(" trim(t.MA_TRANS_CD) not in ('1161','1261','1361','1461','1561','1661') "));
			
			if(Constance.isNotEmpty(minDate)){
				filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts >= to_date('"+minDate.trim()+"', 'yyyymmdd')"));
	         	
//				filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts","to_date('"+minDate.trim()+"', 'yyyymmdd')",">=", false));
	       	}
			
			
			if(Constance.isNotEmpty(maxDate)){
//				filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts","to_date('"+maxDate.trim()+"', 'yyyymmdd')","<=", false));
				filedBeanList.add(new WhereHQLFieldBean("t.Rec_Crt_Ts < to_date('"+maxDate.trim()+"', 'yyyymmdd' )+1 "));
	       	}
			
			
			/*if(Constance.isNotEmpty(maxDate)){
	         	filedBeanList.add(new WhereHQLFieldBean("t.TRANS_DT",maxDate.trim(),"<=", false));
	       	}
			*/
			
			/*if(Constance.isNotEmpty(obj)){
				String allOp="(";
				String[] Strs=obj.split(",");
				int objLength=(Strs.length)/100;//整数
				int remainder=(Strs.length)%100;//余数
			    if(remainder>0 ){
					String str="";
					for (int i = 0; i <remainder; i++) {
						str=str+Strs[100*objLength+i];
						if(i<remainder-1){
							str=str+",";
						}
					}
					allOp=allOp+"tm.EXT1 IN ("+str+")";
				}else if(remainder==0 && objLength>0){//整除
					String str="";
					for (int i = 0; i <objLength; i++) {
						for (int j = 0; j <100; j++) {
							str=str+Strs[i*100+j];
							if(j<99){
								str=str+"',";
							}
						}
						String or="";
						if(remainder==0 && objLength>1){or="OR";}
						allOp=allOp+or+" tm.EXT1 IN ("+str+")";
					}
				}
			    allOp=allOp+")";
			    filedBeanList.add(new WhereHQLFieldBean(allOp));
//				filedBeanList.add(new WhereHQLFieldBean(" tm.ext1 IN ("+obj+") "));
			}*/
			if(Constance.isNotEmpty(opId)){//操作员 
		    	filedBeanList.add(new WhereHQLFieldBean("tm.rec_upd_opr", opId.trim(), "=", false));
		    }
			
			if(Constance.isNotEmpty(ucbccdArea)){//运维人员
				filedBeanList.add(new WhereHQLFieldBean("tm.uc_bc_cd_area", ucbccdArea.trim(), "=", false));
			}
			
			if (Constance.isNotEmpty(retriRefNo)) {
			    filedBeanList.add(new WhereHQLFieldBean("t.RETRI_REF_NO", retriRefNo.trim(), "=", false));	
			}
			if (Constance.isNotEmpty(retriRefNoConv)) {//交易参考号
			    filedBeanList.add(new WhereHQLFieldBean("T.RETRI_REF_NO_CONV", retriRefNoConv.trim(), "=", false));	
			}
			
			return filedBeanList;
		}


		public String getMerNo() {
			return merNo;
		}

		public void setMerNo(String merNo) {
			this.merNo = merNo;
		}

		public String getMerName() {
			return merName;
		}

		public void setMerName(String merName) {
			this.merName = merName;
		}

		public String getEtsterMid() {
			return etsterMid;
		}

		public void setEtsterMid(String etsterMid) {
			this.etsterMid = etsterMid;
		}

		public String getEtstranCode() {
			return etstranCode;
		}

		public void setEtstranCode(String etstranCode) {
			this.etstranCode = etstranCode;
		}

		public String getRespCode() {
			return respCode;
		}

		public void setRespCode(String respCode) {
			this.respCode = respCode;
		}

		public String getMinRMB() {
			return minRMB;
		}

		public void setMinRMB(String minRMB) {
			this.minRMB = minRMB;
		}

		public String getMaxRMB() {
			return maxRMB;
		}

		public void setMaxRMB(String maxRMB) {
			this.maxRMB = maxRMB;
		}

		
		/*public String getObj() {
			return obj;
		}

		public void setObj(String obj) {
			this.obj = obj;
		}*/

		public String getOrgCode() {
			return orgCode;
		}

		public void setOrgCode(String orgCode) {
			this.orgCode = orgCode;
		}

		public String getAccount() {
			return account;
		}

		public void setAccount(String account) {
			this.account = account;
		}

		public String getTranDate() {
			return tranDate;
		}

		public void setTranDate(String tranDate) {
			this.tranDate = tranDate;
		}

		public String getMinDate() {
			return minDate;
		}

		public void setMinDate(String minDate) {
			this.minDate = minDate;
		}

		public String getMaxDate() {
			return maxDate;
		}

		public void setMaxDate(String maxDate) {
			this.maxDate = maxDate;
		}

		public String getOpId() {
			return opId;
		}

		public void setOpId(String opId) {
			this.opId = opId;
		}

		public String getAgentId() {
			return agentId;
		}

		public void setAgentId(String agentId) {
			this.agentId = agentId;
		}

		public String getRoleType() {
			return roleType;
		}

		public void setRoleType(String roleType) {
			this.roleType = roleType;
		}

		public String getMarketorId() {
			return marketorId;
		}

		public void setMarketorId(String marketorId) {
			this.marketorId = marketorId;
		}

		public String getUcbccdArea() {
			return ucbccdArea;
		}

		public void setUcbccdArea(String ucbccdArea) {
			this.ucbccdArea = ucbccdArea;
		}

		public String getRetriRefNo() {
			return retriRefNo;
		}

		public void setRetriRefNo(String retriRefNo) {
			this.retriRefNo = retriRefNo;
		}
		public String getMchntCdConv() {
			return mchntCdConv;
		}

		public void setMchntCdConv(String mchntCdConv) {
			this.mchntCdConv = mchntCdConv;
		}
		public String getRetriRefNoConv() {
			return retriRefNoConv;
		}

		public void setRetriRefNoConv(String retriRefNoConv) {
			this.retriRefNoConv = retriRefNoConv;
		}	
}
